package com.telecom.cms.actions;

import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DispatchAction;
import org.apache.struts.upload.FormFile;

import com.ncr.teradata.bireport.CommonFunc;
import com.telecom.cms.dao.GetConnect;
import com.telecom.cms.dao.IdaCustomDAO;
import com.telecom.cms.dao.IdaDAO;
import com.telecom.cms.dao.IdaLogDAO;
import com.telecom.cms.dto.IdaBooking;
import com.telecom.cms.dto.IdaTemplate;
import com.telecom.cms.dto.SEG_BIZ_ATOM_DEFDTO;
import com.telecom.cms.dto.SysLocLatn;
import com.telecom.cms.forms.IdaCustomForm;
import com.telecom.cms.service.IdaMgr;
import com.telecom.cms.util.Constant;
import com.telecom.cms.util.CurrentTime;
import com.telecom.cms.util.FileOperate;

public class IdaCustomAction extends DispatchAction 
{
	private IdaMgr idamgr = new IdaMgr();
	
	private void getCommon(HttpServletRequest request){
		List templateList = idamgr.findAllTemplate();
		request.setAttribute("templateList", templateList);
		
		String templateId = request.getParameter("templateId");
		if (templateId != null && !templateId.equals("")){
			int tid = Integer.parseInt(templateId);
			IdaTemplate template = idamgr.findTemplate(tid);
			if (template != null){
				request.setAttribute("idaTemplate", template);
			}
		}
	}
	
	public ActionForward runIdaCustom(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response) {
		getCommon(request);
		
		List billingCycleIdList = new ArrayList();
		List latnCdList = new ArrayList();
		IdaDAO idadao = new IdaDAO();
		for (int i = -1; i >= -12; i--){
			String m = CommonFunc.convertDate(CommonFunc.getToday("yyyyMMdd"), "yyyyMMdd", Calendar.MONTH, i).substring(0,6);
			billingCycleIdList.add(m);
		}
		List latns = idadao.findLatn((String)request.getSession().getAttribute("Latn_Id"));
		for (int i = 0; i < latns.size(); i++){
			SysLocLatn locLatn = (SysLocLatn)latns.get(i);
			if (locLatn.getLatn_Cd().equalsIgnoreCase("Z"))
				continue;
			latnCdList.add(locLatn);
		}
		
		request.setAttribute("billingCycleIdList",billingCycleIdList);
		request.setAttribute("latnCdList",latnCdList);
		request.getSession().setAttribute("AccsLogId", 
				IdaLogDAO.logAccs(((Long)request.getSession().getAttribute("LoginLogId")).longValue(),
				-99,"13","自定义取数"));
		return mapping.findForward("runIdaCustom");
	}
	
	public ActionForward doQuery(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response) {
		String custSql = "";
		IdaCustomForm icForm = (IdaCustomForm)form;
		if (icForm.getCustomType().equals("numCheck"))
			custSql = numCheck(icForm,request,response);
		else if (icForm.getCustomType().equals("custSql"))
			custSql = custSql(icForm,request,response);
		else if (icForm.getCustomType().equals("upFile"))
			custSql = upFile(icForm,request,response);
		else ;
		
		IdaDAO idadao = new IdaDAO();
		String billingCycleId = request.getParameter("billingCycleId");
		String latnCd = request.getParameter("latnCd");
		String queryType = request.getParameter("queryType");
		
		String basTable = "";
		String basColumn = "";
		boolean basTableIsCurrent = false;;
		if (icForm.getAnlsTypeId().equals("10")){
			basTable = Constant.getStPara("bastablename");
			basColumn = Constant.getStPara("PrdIdColName");
			if (Constant.getStPara("BasTableIsCurrent").trim().equalsIgnoreCase("YES"))
				basTableIsCurrent = true;
		}
		else if(icForm.getAnlsTypeId().equals("20")){
			basTable = Constant.getStPara("BasCustTable");
			basColumn = Constant.getStPara("CustIdColName");
			if (Constant.getStPara("BasCustTableIsCurrent").trim().equalsIgnoreCase("YES"))
				basTableIsCurrent = true;
		}
		else ;
		
		StringBuffer sql = new StringBuffer();
		StringBuffer selectStr = new StringBuffer();
		StringBuffer fromStr = new StringBuffer();
		StringBuffer whereStr = new StringBuffer();
		String groupBy = "";
		String orderBy = "";
		Set basTableSet = new TreeSet();
		Set dmnTableSet = new TreeSet();
		List resultAtoms = new ArrayList();
		List paramValues = new ArrayList();
		paramValues.add(new String[]{"帐务月",billingCycleId});
		StringBuffer filtResult = new StringBuffer();
		
		String[] rsAtoms = request.getParameterValues("ObjSelect");
		if (rsAtoms != null){
			Map atomValueMap = idadao.findAtomValueMap();
			String sumStart = queryType.equals("20") ? "SUM(" : "";
			String sumEnd = queryType.equals("20") ? ")" : "";
			selectStr.append("SELECT \n");
			for (int i = 0; i < rsAtoms.length; i++){
				String orderRule = "";
				if (rsAtoms[i].indexOf("#") != -1){
					orderRule = rsAtoms[i].substring(rsAtoms[i].indexOf("#") + 1);
					rsAtoms[i] = rsAtoms[i].substring(0,rsAtoms[i].indexOf("#"));
				}
				
				SEG_BIZ_ATOM_DEFDTO atom = idadao.findAtom(Integer.parseInt(rsAtoms[i]));
				resultAtoms.add(atom);
				String colName = atom.getBAS_TABLE_COL_NAME().trim();
				String tableName = atom.getBAS_TABLE_NAME().trim();
				if (atom.getSRC_TABLE_COL_NAME() != null && !atom.getSRC_TABLE_COL_NAME().trim().equals("")){
					colName = atom.getSRC_TABLE_COL_NAME().trim();
					tableName = atom.getCDE_SRC_TABLE_NAME().trim();
					dmnTableSet.add(tableName + "#" + atom.getBAS_TABLE_NAME().trim() + "#" + atom.getBAS_TABLE_COL_NAME().trim());
				}
				if (i > 0)
					selectStr.append("\t,");
				else
					selectStr.append("\t");
				String atomFlag = atom.getBIZ_ATOM_FLAG().trim();
				if (atomFlag.equalsIgnoreCase("MONEY")){
					selectStr.append(sumStart + tableName + "." + colName + sumEnd + "/100 \n");
				}
				else if (atomFlag.equalsIgnoreCase("TIME")){
					selectStr.append(sumStart + tableName + "." + colName + sumEnd + "/60 \n");
				}
				else if (atomFlag.equalsIgnoreCase("COUNT")){
					selectStr.append(sumStart + tableName + "." + colName + sumEnd + " \n");
				}
				else
					selectStr.append(tableName + "." + colName + " \n");
				
				basTableSet.add(atom.getBAS_TABLE_NAME().trim());
				if (queryType.equals("20")){
					if (!(atomFlag.equalsIgnoreCase("MONEY") || atomFlag.equalsIgnoreCase("TIME") || atomFlag.equalsIgnoreCase("COUNT"))){
						if (groupBy.equals(""))
							groupBy = "GROUP BY " + (i + 1);
						else
							groupBy += "," + (i + 1);
					}
				}
				if (!orderRule.trim().equals("")){
					if (orderBy.equals(""))
						orderBy = "ORDER BY " + (i + 1) + " " + orderRule;
					else
						orderBy += "," + (i + 1) + " " + orderRule;
				}
				
				filtResult.append("<tr><td>" + atom.getBIZ_ATOM_NAME() + "</td><td>");
				if (atomFlag.equalsIgnoreCase("MONEY") || atomFlag.equalsIgnoreCase("TIME") || atomFlag.equalsIgnoreCase("COUNT")){
					filtResult.append(buildHtmlControl("14",String.valueOf(atom.getBIZ_ATOM_ID()),"","",null,""));
				}
				else{
					List atomValues = (List)atomValueMap.get(String.valueOf(atom.getBIZ_ATOM_ID()));
					String showType = atomValues.size() == 0 ? "10" : (atomValues.size() > 10 ? "17" : "13");
					filtResult.append(buildHtmlControl(showType,String.valueOf(atom.getBIZ_ATOM_ID()),"","",atomValues,""));
				}
				filtResult.append("</td></tr>");
			}
			if (!groupBy.equals(""))
				groupBy += " \n";
		}
		
		if (!custSql.trim().equals("")){
			whereStr.append("WHERE \t" + basTable + "." + icForm.getNumType() + " IN \n(\n" + custSql + "\n)\n");
			String condType = icForm.getCustomType();
			condType = condType.equals("numCheck") ? "号码反查" : (condType.equals("custSql") ? "自定义SQL" : "上传文件");
			paramValues.add(new String[]{"用户自定义条件","限制方式：" + condType + "， 限制内容：" + icForm.getNumType()});
		}
		if (!basTableIsCurrent){
			String preFix = whereStr.toString().trim().equals("") ? "WHERE \t" : "AND \t";
			whereStr.append(preFix + basTable + ".Billing_Cycle_Id = '" + billingCycleId + "' \n");
		}
		
		String mmTable = "";
		String multiMonthStr = "";
		String viewMultiMonth = request.getParameter("viewMultiMonth");
		if (viewMultiMonth != null && viewMultiMonth.equalsIgnoreCase("T")){
			String[] viewMonth = request.getParameterValues("viewMonth");
			if (viewMonth != null && viewMonth.length > 0){
				for (int i = 0; i < viewMonth.length; i++){
					if (i > 0)
						multiMonthStr += ",";
					multiMonthStr += "'" + viewMonth[i] + "'";
				}
			}
		}
		
		fromStr.append("FROM\t\t" + basTable + "\n");
		Object[] tables = basTableSet.toArray();
		for (int i = 0; i < tables.length; i++){
			if (tables[i].toString().equalsIgnoreCase(basTable)){
				continue;
			}
			else{
				fromStr.append("LEFT JOIN\t" + tables[i] + "\n");
				fromStr.append("ON\t\t" + basTable + "." + basColumn + " = " + tables[i] + "." + basColumn + " \n");
				if (Constant.getStPara("NoMonthJoinTable").indexOf(tables[i].toString()) == -1){
					if (multiMonthStr.trim().equals("")){
						fromStr.append("AND\t\t" + tables[i] + ".Billing_Cycle_Id = '" + billingCycleId + "' \n");
					}
					else{
						fromStr.append("AND\t\t" + tables[i] + ".Billing_Cycle_Id IN (" + multiMonthStr + ") \n");
						if (mmTable.trim().equals(""))
							mmTable = tables[i].toString();
						else
							fromStr.append("AND\t\t" + tables[i] + ".Billing_Cycle_Id = " + mmTable + ".Billing_Cycle_Id \n");
					}
				}
			}
		}
		Object[] dmnTables = dmnTableSet.toArray();
		for (int i = 0; i < dmnTables.length; i++){
			String[] dt = dmnTables[i].toString().split("#");
			fromStr.append("LEFT JOIN\t" + dt[0] + "\n");
			fromStr.append("ON\t\t" + dt[1] + "." + dt[2] + " = " + dt[0] + "." + dt[2] + " \n");
		}
		
		sql.append(selectStr.toString());
		sql.append(fromStr.toString());
		sql.append(whereStr.toString());
		sql.append(groupBy);
		sql.append(orderBy);
		String idaSql = sql.toString();
		idaSql = idaSql.replaceAll("_Z ", "_"+latnCd+" ");
		idaSql = idaSql.replaceAll("_Z\\.", "_"+latnCd+".");
		idaSql = idaSql.replaceAll("_Z,", "_"+latnCd+",");
		
		int pageNo = 1;
		int pageSize = 100;
		String startTime = CurrentTime.getCurrentTime();
		List idaData = idadao.idaQuery(idaSql, pageNo, pageSize, true);
		String endTime = CurrentTime.getCurrentTime();
		int rowCount = ((Integer)idaData.get(0)).intValue();
		int pageCount = rowCount % pageSize == 0 ? rowCount / pageSize : rowCount / pageSize + 1;
		idaData.remove(0);
		
		request.setAttribute("idaData", idaData);
		request.getSession().setAttribute("idaSql", idaSql);
		request.getSession().setAttribute("resultAtoms", resultAtoms);
		request.getSession().setAttribute("paramValues", paramValues);
		request.getSession().setAttribute("filtResult", filtResult.toString());
		request.getSession().setAttribute("pageNo", new Integer(pageNo));
		request.getSession().setAttribute("pageCount", new Integer(pageCount));
		request.getSession().setAttribute("rowCount", new Integer(rowCount));
		request.getSession().setAttribute("idaLatnCd", latnCd);
		request.getSession().removeAttribute("resultFiltSql");
		request.getSession().removeAttribute("instanceResultAtoms");
		request.getSession().removeAttribute("instanceParamValues");
		request.getSession().removeAttribute("instanceTemplateId");
		
		IdaLogDAO.logExecute(((Long)request.getSession().getAttribute("AccsLogId")).longValue(), "11", idaSql, rowCount, startTime, endTime, "");
		return mapping.findForward("idaResult");
	}
	
	public ActionForward runIdaBooking(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response) {
		List bookingList = IdaCustomDAO.findAllBooking();
		request.setAttribute("bookingList", bookingList);
		
		String bookingId = request.getParameter("bookingId");
		if (bookingId != null){
			for (int i = 0; i < bookingList.size(); i++){
				IdaBooking booking = (IdaBooking)bookingList.get(i);
				if (booking.getBookingId() == Integer.parseInt(bookingId)){
					request.setAttribute("booking", booking);
					break;
				}
			}
		}
		return mapping.findForward("idaBooking");
	}
	
	public ActionForward insertBooking(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response) {
		IdaBooking booking = new IdaBooking();
		booking.setBookingName(request.getParameter("bookingName"));
		booking.setRequirement(request.getParameter("requirement"));
		booking.setPurpose(request.getParameter("purpose"));
		booking.setTimeLimit(request.getParameter("timeLimit"));
		booking.setBookingUser(((Integer)request.getSession().getAttribute("User_Id")).toString());
		booking.setBookingTime(CurrentTime.getCurrentTime());
		booking.setStatus("新建");
		IdaCustomDAO.insertBooking(booking);
		
		return runIdaBooking(mapping,form,request,response);
	}
	
	public ActionForward updateBooking(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response) {
		IdaBooking booking = new IdaBooking();
		booking.setBookingId(Integer.parseInt(request.getParameter("bookingId")));
		booking.setBookingName(request.getParameter("bookingName"));
		booking.setRequirement(request.getParameter("requirement"));
		booking.setPurpose(request.getParameter("purpose"));
		booking.setTimeLimit(request.getParameter("timeLimit"));
		booking.setProcessResult(request.getParameter("processResult"));
		if (!request.getParameter("processResult").trim().equals("")){
			booking.setProcessUser(((Integer)request.getSession().getAttribute("User_Id")).toString());
			booking.setProcessTime(CurrentTime.getCurrentTime());
		}
		booking.setStatus(request.getParameter("status"));
		IdaCustomDAO.updateBooking(booking);
		
		return runIdaBooking(mapping,form,request,response);
	}
	
	public ActionForward deleteBooking(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response) {
		IdaBooking booking = new IdaBooking();
		booking.setBookingId(Integer.parseInt(request.getParameter("bookingId")));
		IdaCustomDAO.deleteBooking(booking);
		
		return runIdaBooking(mapping,form,request,response);
	}
	
	private String buildHtmlControl(String showType,String pName,String pValue,String pDisable,List atomValues,String pNameCn){
		String[] pvs = pValue.split("，");
		StringBuffer control = new StringBuffer();
		if (showType.equals("10")){ //text
			control.append("<input type='text' name='" + pName + "' value='" + pValue + "' " + pDisable + ">");
		}
		else if (showType.equals("11")){ //select
			control.append("<select name='" + pName + "' " + pDisable + ">");
			control.append("<option value=''>请选择</option>");
			for (int j = 0; j < atomValues.size(); j++){
				String[] value = (String[])atomValues.get(j);
				if (value[2].equals(""))
					control.append("<option value='" + value[0] + "' " + (pValue.equals(value[0]) ? "selected" : "") + ">" + value[1] + "</option>");
				else
					control.append("<option value='" + value[0] + "' " + (pValue.equals(value[0]) ? "selected" : "") + ">" + value[1] + "－" + value[2] + "</option>");
			}
			control.append("</select>");
		}
		else if (showType.equals("12")){ //radio
			for (int j = 0; j < atomValues.size(); j++){
				String[] value = (String[])atomValues.get(j);
				control.append("<input type='radio' name='" + pName + "' value='" + value[0] + "' " + (pValue.equals(value[0]) ? "checked " : " ") + pDisable + ">" + value[1] + "&nbsp;&nbsp;");
			}
		}
		else if (showType.equals("13")){ //checkbox
			for (int j = 0; j < atomValues.size(); j++){
				String[] value = (String[])atomValues.get(j);
				String checked = " ";
				for (int k = 0; k < pvs.length; k++){
					if (pvs[k].equals(value[0])){
						checked = "checked ";
						break;
					}
				}
				if (value[2].equals(""))
					control.append("<input type='checkbox' name='" + pName + "' value='" + value[0] + "' " + checked + pDisable + ">" + value[1] + "&nbsp;&nbsp;");
				else
					control.append("<input type='checkbox' name='" + pName + "' value='" + value[0] + "' " + checked + pDisable + ">" + value[1] + "－" + value[2] + "&nbsp;&nbsp;");
			}
		}
		else if (showType.equals("14")){ //between text input
			if (pvs.length < 1){
				control.append("<input type='text' name='" + pName + "' value='" + "" + "' " + pDisable + " size='5'> 至 ");
				control.append("<input type='text' name='" + pName + "' value='" + "" + "' " + pDisable + " size='5'>");
			}
			else if (pvs.length < 2){
				control.append("<input type='text' name='" + pName + "' value='" + pvs[0] + "' " + pDisable + " size='5'> 至 ");
				control.append("<input type='text' name='" + pName + "' value='" + "" + "' " + pDisable + " size='5'>");
			}
			else{
				control.append("<input type='text' name='" + pName + "' value='" + pvs[0] + "' " + pDisable + " size='5'> 至 ");
				control.append("<input type='text' name='" + pName + "' value='" + pvs[1] + "' " + pDisable + " size='5'>");
			}
		}
		else if (showType.equals("15")){ //tree
			control.append("<a href=\"javascript:showHiddenDiv('div" + pName + "');\">请选择（单击可显示或隐藏选项）</a>");
			control.append("<div style='display: none' id='div" + pName + "'>");
			control.append("<script>");
			control.append("var Tree" + pNameCn + " = new dTree('Tree" + pNameCn + "');");
			for (int j = 0; j < atomValues.size(); j++){
				String[] value = (String[])atomValues.get(j);
				String checked = " ";
				for (int k = 0; k < pvs.length; k++){
					if (pvs[k].equals(value[0])){
						checked = "checked ";
						break;
					}
				}
				control.append("Tree" + pNameCn + ".add('" + value[0] + "','" + value[1] + "',");
				control.append("\"<input type='checkbox' " +
					"name='" + pName + "' id='" + "Tree" + pNameCn + j + "' value='" + value[0] + "' " +
					"onclick='changeChild(Tree" + pNameCn + "," + "Tree" + pNameCn + j + ");" +
					"changeParent(Tree" + pNameCn + "," + "Tree" + pNameCn + j + ");' " + pDisable +
					" " + checked + ">" + value[2] + "\");");
			}
			control.append("document.write(Tree" + pNameCn + ");");
			control.append("</script>");
			control.append("</div>");
		}
		else if (showType.equals("16")){ //date select
			control.append("<input type='text' id='" + pName + "' name='" + pName + "' value='" + pValue + "' " + pDisable + " " +
					"onfocus=\"show_cele_date('" + pName + "','','','" + pName + "')\">");
		}
		else if (showType.equals("17")){ //div list checkbox
			control.append("<div style='height: 100px; overflow-y: auto;'>");
			for (int j = 0; j < atomValues.size(); j++){
				String[] value = (String[])atomValues.get(j);
				String checked = " ";
				for (int k = 0; k < pvs.length; k++){
					if (pvs[k].equals(value[0])){
						checked = "checked ";
						break;
					}
				}
				if (j > 0) control.append("<br>");
				if (value[2].equals(""))
					control.append("<input type='checkbox' name='" + pName + "' value='" + value[0] + "' " + checked + pDisable + ">" + value[1] + "&nbsp;&nbsp;");
				else
					control.append("<input type='checkbox' name='" + pName + "' value='" + value[0] + "' " + checked + pDisable + ">" + value[1] + "－" + value[2] + "&nbsp;&nbsp;");
			}
			control.append("</div>");
		}
		else ;
		return control.toString();
	}

	private String numCheck(IdaCustomForm form,HttpServletRequest request,HttpServletResponse response) {
		String custSql = "";
		String nums = form.getInputText();
		String numArray[] = null;
		if (nums != null) {
			numArray = nums.split("\r\n");
		}
		if (numArray.length <= 1000) {
			for (int i = 0; i < numArray.length; i++) {
				if (i > 0)
					custSql += ",";
				custSql += "'" + numArray[i].trim() + "'";
			}
		} else {
			String user_id = (String)request.getSession().getAttribute("User_Id");
			custSql = tpump(numArray, custSql, user_id);
		}
		return custSql;
	}

	private String custSql(IdaCustomForm form,HttpServletRequest request,HttpServletResponse response) {
		String custSql = form.getInputText();
		return custSql;
	}

	private String upFile(IdaCustomForm form,HttpServletRequest request,HttpServletResponse response) {
		String custSql = "";
		String fileType = form.getFileType();
		FormFile file = form.getUpFile();	//取得上传的文件
		String numArray[] = null;
		String userId = request.getSession().getAttribute("User_Id").toString();
		String encoding = request.getCharacterEncoding();
		if ((encoding != null) && (encoding.equalsIgnoreCase("utf-8"))) {
			response.setContentType("text/html; charset=gb2312");	//如果没有指定编码，编码格式为gb2312
		}
		try {
			InputStream stream = file.getInputStream();	//把文件读入
			String filePath = request.getRealPath("/") + "/upfiles/idacustom";	//取当前系统路径
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			OutputStream bos = new FileOutputStream(filePath + "/" + file.getFileName() + userId);	//建立一个上传文件的输出流
			int bytesRead = 0;
			byte[] buffer = new byte[8192];
			while ((bytesRead = stream.read(buffer, 0, 8192)) != -1) {
				bos.write(buffer, 0, bytesRead);	//将文件写入服务器
			}
			bos.close();
			stream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		if (fileType.equals("txt")) {	//如果是txt文本
			try {
				InputStream in = file.getInputStream();
				StringBuffer cb = new StringBuffer();
				byte[] bs = new byte[8192];	//设置缓存
				while (true) {
					int count = in.read(bs);
					if (count == -1) {
						break;
					} else {
						cb.append(new String(bs, 0, count));
					}
					in.close();
					numArray = cb.toString().split("\r\n");
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
			if (numArray.length <= 1000) {
				for (int i = 0; i < numArray.length; i++) {
					if (i > 0)
						custSql += ",";
					custSql += "'" + numArray[i].trim() + "'";
				}
			}
			else {
				custSql = tpump(numArray, custSql, userId);
			}
		} 
		else if (fileType.equals("excel")) {	//如果上传的文件是excel
			List numArrayList = new ArrayList();
			try {
				// 创建对Excel工作簿文件的引用
				HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
				// 创建对工作表的引用。
				for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
					HSSFSheet sheet = workbook.getSheetAt(i);
					for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
						HSSFRow row = sheet.getRow(j);
						if (row != null) {
							HSSFCell cell = row.getCell((short)0);
							if (cell != null) {
								String value = getCellStringValue(cell).trim();
								numArrayList.add(value);
							}
						}
					}
				}
				if (numArrayList.size() <= 1000) {
					for (int i = 0; i < numArrayList.size(); i++) {
						if (i > 0)
							custSql += ",";
						custSql += "'" + numArrayList.get(i) + "'";
					}
				}
				else {
					numArray = (String[])numArrayList.toArray();
					custSql = tpump(numArray, custSql, userId);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return custSql;
	}

	private String getCellStringValue(HSSFCell cell) {
		String cellValue = "";
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:
				cellValue = cell.getStringCellValue();
				if (cellValue.trim().equals("") || cellValue.trim().length() <= 0)
					cellValue = " ";
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				//cellValue = String.valueOf(cell.getNumericCellValue());
				cellValue = new BigDecimal(cell.getNumericCellValue()).toPlainString();
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				//cellValue = String.valueOf(cell.getNumericCellValue());
				cellValue = new BigDecimal(cell.getNumericCellValue()).toPlainString();
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				cellValue = " ";
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				break;
			case HSSFCell.CELL_TYPE_ERROR:
				break;
			default:
				break;
		}
		return cellValue;
	}

	private String tpump(String[] numArray, String custSql, String user_id) {
		String num = "";
		Connection con = GetConnect.getCsConnection();
		String nowtime = CurrentTime.getCurrentTime();
		FileOperate opa = new FileOperate();
		PreparedStatement psmt = null;
		try {
			psmt = con.prepareStatement(" CREATE SET TABLE PD_CMS.NUM"
							+ user_id
							+ nowtime
							+ " ,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT( INSERT_VALUE CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC TITLE '号码值')");
			psmt.executeUpdate();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		for (int i = 0; i < numArray.length; i++) {
			num = num + numArray[i] + "|\r\n";
		}
		opa.newFile("D:/cmslog/" + user_id + nowtime + ".txt", num);

		System.out
				.println("***************************creat numtxt ok !!************************");

		opa
				.newFile(
						"D:/cmslog/" + user_id + nowtime + ".tpump",
						".LOGTABLE PD_CMS.L"
								+ user_id
								+ nowtime
								+ "LOG;<br> .LOGON dbc/pu_cms,yuancms1271;<br> .begin LOAD  ERRLIMIT 10<br>CHECKPOINT 1 <br>SESSIONS 1 <br>TENACITY 2 <br>pack 10 <br>SERIALIZE ON <br> errortable PD_CMS.E"
								+ user_id
								+ nowtime
								+ "ERO; <br>.layout Lay1; <br>.field insert_value * VARCHAR(30) KEY; <br>.dml label dml1; <br>insert PD_CMS.NUM"
								+ user_id
								+ nowtime
								+ " (insert_value) values ( :insert_value); <br>.import infile \""
								+ "D:/cmslog/"
								+ user_id
								+ nowtime
								+ ".txt\""
								+ "<br>format vartext '|'"
								+ "  <br>layout Lay1  <br>apply dml1;  <br>.end LOAD; <br>.LOGOFF;");

		System.out
				.println("***************************creat controlfile ok !!*********************");

		opa.newFile(user_id + nowtime + ".bat", " tpump < D:/cmslog/" + user_id
				+ nowtime + ".tpump");
		System.out
				.println("***************************creat batfile ok !!***********************");

		try {
			Runtime r = Runtime.getRuntime();
			Process p = r.exec(user_id + nowtime + ".bat");
			BufferedReader bf = new BufferedReader(new InputStreamReader(p
					.getInputStream()));

			String line = null;
			while ((line = bf.readLine()) != null) {
				System.out.println(line);

			}

			System.out
					.println("******************************tpump data ok************************************");

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		custSql = custSql + " in ( select INSERT_VALUE from PD_CMS.NUM"
				+ user_id + nowtime + " )";
		return custSql;
	}
}